﻿// =================================================================== 
// 项目说明
//====================================================================
// 文件： ERP_Cargo.cs
// 创建时间：2016/11/21
// 负责人：
// ===================================================================
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using CRMMis.Model;
using CRMMis.IDAL;


namespace MSSQLDAL
{
    /// <summary>
    /// 数据层实例化接口类  商品目录表.
    /// </summary>
    public partial class ERP_CargoDAL : IERP_CargoDAL
    {
		//此处写查询数据的sql语句，


        //if QueryDataString==""，DAL层的GetList，GetRecordCount等函数将直接存取ERP_Cargo表


        //if QueryDataString!=""，则DAL层的GetList，GetRecordCount等函数将存取该SQL语句，取代表
		//注意：所写sql语句执行结果的字段必须是唯一的

		private const string QueryDataString = @"SELECT
                a.CargoId,
                a.CargoName,
                a.Spell,
                a.ByName,
                a.CargoCode,
                a.Spec,
                a.Version,
                a.TypeId,
                a.UseId,
                a.Area,
                a.Unit,
                a.IsImported,
                a.Pic,
                a.InUser,
                a.InDate,
                a.DelUser,
                a.DelDate,
                a.DelState,
                a.ProduceName,
                a.State,
                b.Type_Name,
                c.Use_NAME,
                d.USER_NAME Inuser_Name,
                e.USER_NAME Deluser_Name,
                CASE a.IsImported WHEN 0 THEN '国产' ELSE '进口' END IsImport,
                (select top 1 DetailId from ERP_Cargo_Detail where a.CargoId=CargoId order by DetailId desc) DetailId,
                (select top 1 Supplyprice from ERP_Cargo_Detail where a.CargoId=CargoId order by DetailId desc) Supplyprice,
                (select top 1 SupplyId from ERP_Cargo_Detail where a.CargoId=CargoId order by DetailId desc) SupplyId,
                (select SupplyName from ERP_Supply where SupplyID=(select top 1 SupplyId from ERP_Cargo_Detail where a.CargoId=CargoId order by DetailId desc)) SupplyName,
                (select top 1 State from ERP_Cargo_Detail where a.CargoId=CargoId order by DetailId desc) Detail_State
                FROM ERP_Cargo a 
                LEFT JOIN ERP_Type b ON a.TypeId=b.Type_Id
                LEFT JOIN ERP_Use c ON a.UseId=c.Use_ID
                LEFT JOIN SYS_USER d ON a.InUser=d.USER_ID
                LEFT JOIN SYS_USER e ON a.DelUser=e.USER_ID";
	}
}
